/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package sk.lieskove.jianghongtiao.paris.webclient.model;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.stereotype.Repository;
import sk.lieskove.jianghongtiao.websearch.enums.DocumentType;
import sk.lieskove.jianghongtiao.paris.webclient.persistence.ClassificationItem;
import sk.lieskove.jianghongtiao.websearch.document.preprocessing.SupportedLanguages;

import javax.sql.DataSource;
import java.io.Serializable;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import sk.lieskove.jianghongtiao.commonpersist.Persist;
import sk.lieskove.jianghongtiao.paris.webclient.persistence.ClassificationUser;

/**
 * 
 * @author xjuraj e-mail: jjurco.sk_gmail.com
 */
@Repository("documentManager")
public class DocumentManagerDefault implements Serializable, DocumentManager {
    
    private static final String COLUMNS_STRING = 
                "c.id as csId, c.documentType as documentType, "
                + "w.id as wsdId, "
                + "w.serviceName as serviceName, "
                + "w.uuid as uuid, "
                + "w.url as url, "
                + "w.searchPosition as searchPosition, "
                + "w.title as title, "
                + "w.description as description, "
                + "w.proxyString as proxyString, "
                + "w.encoding as encoding, "
                + "w.languageName as languageName, "
                + "w.mimeType as mimeType, "
                + "w.responseStatusCode as responseStatusCode, "
                + "w.fileLocation as fileLocation, "
                + "w.originalFileLocation as originalFileLocation, "
                + "w.fileSize as fileSize, "
                + "w.fetchTime as fetchTime, "
                + "w.searchQuery as searchQuery, "
                + "w.loadingTime as loadingTime, "
                + "w.excludeReason as excludeReason";
    private static final String SELECT_ALL_STRING = "SELECT " + COLUMNS_STRING
                + " FROM websearchresponse w "
                + " LEFT OUTER JOIN classificationstorage c"
                + " ON c.websearchresponse_id=w.id";
    private static final String SELECT_NOT_EXCLUDED=SELECT_ALL_STRING + 
            " WHERE w.excludereason IS NULL";
    private static final String SELECT_NOT_EXCLUDED_PROCESSED=SELECT_NOT_EXCLUDED+
            " AND c.documenttype != 'UNKNOWN' "+
            " AND c.documenttype IS NOT NULL "+ 
            " AND w.searchquery = ?";
    private static final String SELECT_NOT_EXCLUDED_BY_LANG = SELECT_ALL_STRING+
            " WHERE (w.excludereason IS NULL "+
            " OR w.excludereason = 'SURNAME_NOT_PRESENT') "+
            " AND c.documenttype != 'UNKNOWN' "+
            " AND c.documenttype IS NOT NULL "+ 
            " AND w.languagename = ?";
    private static final String SELECT_NOT_EXCLUDED_BY_LANG_DOC_TYPE = SELECT_NOT_EXCLUDED_BY_LANG + 
            " AND c.documenttype = ?";
    private static final String SELECT_NOT_PROCESSED_STRING = SELECT_ALL_STRING +
            " WHERE w.excludeReason IS NULL "+
            " AND c.documenttype = 'UNKNOWN' ";
    private static final String SELECT_FIRST_NOT_PROCESSED_STRING = SELECT_NOT_PROCESSED_STRING+
            " ORDER BY RANDOM() LIMIT 1";
    private static final String SELECT_BY_ID_STRING = "SELECT " + COLUMNS_STRING
                + " FROM websearchresponse w "
                + " LEFT OUTER JOIN classificationstorage c"
                + " ON c.websearchresponse_id=w.id"
                + " WHERE c.id=?";
    private static final String UPDATE_CS_STRING=
            "UPDATE classificationstorage SET documenttype = ? WHERE id = ?; ";
    private static final String UPDATE_WSD_STRING=
            "UPDATE websearchresponse     SET languagename = ? WHERE id = ?; ";
    private static final String PROCESS_AGAIN_STRING=
            "UPDATE classificationstorage SET documenttype = 'UNKNOWN' WHERE documenttype IS NULL; ";
    private static final String SELECT_USED_LANGUAGES=
            "SELECT languagename FROM websearchresponse WHERE excludereason IS NULL GROUP BY languagename";
    private static final String SELECT_USED_DOCUMENT_TYPES=
            "SELECT documenttype FROM classificationstorage GROUP BY documenttype";
    private static final String SELECT_SEARCHED_QUERIES=
            "SELECT w.searchquery as searchquery FROM websearchresponse as w, classificationstorage as c"
            + " WHERE w.id = c.websearchresponse_id "
            + " AND c.documenttype != 'UNKNOWN'"
            + " AND c.documenttype IS NOT NULL"
            + " GROUP BY w.searchquery ";
    private static final String SELECT_NUM_REMAINING="SELECT count(*)"
                + " FROM websearchresponse w "
                + " LEFT OUTER JOIN classificationstorage c"
                + " ON c.websearchresponse_id=w.id"
                + " WHERE w.excludeReason IS NULL "
                + " AND c.documenttype = 'UNKNOWN' ";
    
    
    private transient Logger log = LoggerFactory.getLogger(DocumentManagerDefault.class);

    public DocumentManagerDefault() {
        log.debug("Document service created!");
    }
    
    private SimpleJdbcTemplate jdbc;

    @Autowired
    public void setDataSource(DataSource dataSource) {
        log.debug("dataSource set to {}", dataSource);
        jdbc = new SimpleJdbcTemplate(dataSource);
    }
    
    private static final ParameterizedBeanPropertyRowMapper<ClassificationItem> 
            CLASSIFICATION_ITEM_MAPPER = ParameterizedBeanPropertyRowMapper.
            newInstance(ClassificationItem.class);

    public List<ClassificationItem> getSearchResponse() {
        return jdbc.query("SELECT " + COLUMNS_STRING
                + " FROM websearchresponse w "
                + " LEFT OUTER JOIN classificationstorage c"
                + " ON c.websearchresponse_id=w.id", CLASSIFICATION_ITEM_MAPPER);
    }

    public List<ClassificationItem> getAllDocuments() {
        return jdbc.query(SELECT_ALL_STRING, CLASSIFICATION_ITEM_MAPPER);
    }

    public List<ClassificationItem> getNotExcluded() {
        return jdbc.query(SELECT_NOT_EXCLUDED, CLASSIFICATION_ITEM_MAPPER);
    }

    public List<ClassificationItem> getNotProcessed() {
        return jdbc.query(SELECT_NOT_PROCESSED_STRING, CLASSIFICATION_ITEM_MAPPER);
    }
    
    private ClassificationItem selectNotProcessed(){
        try {
           return jdbc.queryForObject(SELECT_FIRST_NOT_PROCESSED_STRING, 
                   CLASSIFICATION_ITEM_MAPPER);
        } catch (DataAccessException ex) {
            log.info("No other item was not found!");
        }
        return null;
    }

    public ClassificationItem getFirstNotProcessed() {
        ClassificationItem ci = selectNotProcessed();
        if(ci == null){
            jdbc.update(PROCESS_AGAIN_STRING);
        } else {
            return ci;
        }
        return selectNotProcessed();
    }

    public ClassificationItem getItem(Long csId) {
        try {
            return jdbc.queryForObject(SELECT_BY_ID_STRING, 
                    CLASSIFICATION_ITEM_MAPPER, new Object[]{csId});
        } catch (DataAccessException ex) {
            log.error("Item with csId: "+csId+" was not found!");
        }
        return null;
    }
    
    public List<String> getUsedLanguageList(){
        List<Map<String, Object>> langList = jdbc.queryForList(SELECT_USED_LANGUAGES);
        List<String> result = new ArrayList<String>();
        for (Iterator<Map<String, Object>> it = langList.iterator(); it.hasNext();) {
            Map<String, Object> map = it.next();
            Object lang = map.get("languagename");
            if(lang != null){
                result.add((String)lang);
            }
        }
        return result;
    }
    
    public List<String> getUsedClasses(){
        List<Map<String, Object>> langList = jdbc.queryForList(SELECT_USED_DOCUMENT_TYPES);
        List<String> result = new ArrayList<String>();
        for (Iterator<Map<String, Object>> it = langList.iterator(); it.hasNext();) {
            Map<String, Object> map = it.next();
            Object typeObject = map.get("documenttype");
            if(typeObject != null){
                result.add((String)typeObject);
            }
        }
        return result;
    }
    
    public int getNumberOfRemaining(){
        return jdbc.queryForInt(SELECT_NUM_REMAINING);
    }
    
    public boolean skipItem(Long csId){
        return 0 < jdbc.update(UPDATE_CS_STRING, null, csId);
    }
    
    public boolean updateItem(Long csId, DocumentType documentType, 
            Long wsdId, SupportedLanguages language, String ip){
        log.debug("Update Item with values: csId:"+csId+" documentType:"+
                documentType+" wsdId:"+wsdId+" language:"+language);
        int i = jdbc.update(UPDATE_CS_STRING, documentType.toString(), csId);
        int j = jdbc.update(UPDATE_WSD_STRING, language.toString(), wsdId);
        Persist p = Persist.getSingleton();
        Date d = new Date();
        p.persist(new ClassificationUser(ip, new Timestamp(d.getTime()), csId));
        return 1 < i+j;
    }

    public List<ClassificationItem> getNotExcludedProcessed(String searchedQuery) {
        return jdbc.query(SELECT_NOT_EXCLUDED_PROCESSED, CLASSIFICATION_ITEM_MAPPER, searchedQuery);
    }
    
    public List<ClassificationItem> getNotExcludedByLanguage(String language){
        return jdbc.query(SELECT_NOT_EXCLUDED_BY_LANG, CLASSIFICATION_ITEM_MAPPER, 
                language);
    }
    public List<ClassificationItem> getNotExcludedByLangDocType(String language, String docType){
        return jdbc.query(SELECT_NOT_EXCLUDED_BY_LANG_DOC_TYPE, CLASSIFICATION_ITEM_MAPPER, 
                language, docType);
    }

    public List<String> getListOfSearchedQueries() {
        List<Map<String, Object>> langList = jdbc.queryForList(SELECT_SEARCHED_QUERIES);
        List<String> result = new ArrayList<String>();
        for (Iterator<Map<String, Object>> it = langList.iterator(); it.hasNext();) {
            Map<String, Object> map = it.next();
            Object lang = map.get("searchquery");
            if(lang != null){
                result.add((String)lang);
            }
        }
        return result;
    }
}
